/**
 * 
 */
package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import ModelLayer.Invoice;
/**
 * @author Ivaylo
 *
 */
public class DBInvoice implements IFDBInvoice{
	private Connection con;
	
	public DBInvoice()
	{
		con = DBConnection1.getInstance().getDBcon();
	}
	
//Implemented methods from the interface
	@Override
	public Invoice getInvoiceByNo(int invoiceNo, boolean retrieveAssociation) {
		Invoice invoice = new Invoice();
		invoice = singleWhere("invoiceNo='"+invoiceNo+"'", retrieveAssociation);
		return invoice;
	}

	@Override
	public int insertInvoice(Invoice invoice) throws Exception 
	{
		String query = "INSERT INTO Invoice(invoiceNo, paymentDate, totalPrice) VALUES('" +
				invoice.getInvoiceNo() +"', '" +
						invoice.getPaymentDate()+"', '" +
								invoice.getPrice()+"')";
		
		int rc = -1;
		System.out.println("insert: " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException ex) {
			System.out.println("Invoice is not inserted");
	        throw new Exception ("Invoice is not inserted correct");
		}
		
		return rc;
	}

	@Override
	public int updateInvoice(Invoice invoice) {
		Invoice invoiceObj = invoice;
		int rc = -1;
		
		String query = "UPDATE Invoice SET " +
				"paymentDate='"+invoiceObj.getPaymentDate()+"', "+
				"totalPrice='"+invoiceObj.getPrice()+"' "+
						"WHERE invoiceNo=" + invoiceObj.getInvoiceNo();
		
		System.out.println("Update query: " + query);
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc=stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("Update exception in Invoice: " + e);
		}
		
		return rc;
	}
	
//Private methods
	private Invoice singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet result;
		Invoice invoice = new Invoice();
		
		String query = buildQuery(wClause);
		System.out.println(query);
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			result = stmt.executeQuery(query);
			
			if (result.next()) {
				invoice = buildInvoice(result);
				stmt.close();
			}
			else
			{//no invoice was found
				invoice = null;
			}
			
		} catch (Exception e) {
			System.out.println("Query Exception: " + e);
		}
		
		return invoice;
	}
	
	//method to build the query
	private String buildQuery(String wClause)
	{
		String query = "SELECT invoiceNo, paymentDate, totalPrice FROM Invoice";
		if (wClause.length()>0) {
			query = query + " WHERE " + wClause;
		}
		
		return query;
	}
	
	//method to build the object
	private Invoice buildInvoice(ResultSet results)
	{
		Invoice invoice = new Invoice();
		try {
			invoice.setInvoiceNo(results.getInt("invoiceNo"));
			invoice.setPaymentDate(results.getString("paymentDate"));
			invoice.setPrice(results.getFloat("totalPrice"));
		} catch (Exception e) {
			System.out.println("error in building the Invoice object");
		}
		return invoice;
	}
}
